<?php
/**********************************************************************
Copyright (C) FrontAccounting, LLC.
Released under the terms of the GNU General Public License, GPL,
as published by the Free Software Foundation, either version 3
of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
// fiscal year routines
function add_fiscalyear($from_date, $to_date, $closed)
{
    $from = date2sql($from_date);
    $to = date2sql($to_date);
    $sql = "INSERT INTO " . TB_PREF . "fiscal_year (begin, end, closed)
        VALUES (" . db_escape($from) . "," . db_escape($to) . ", " . db_escape($closed) . ")";
    db_query($sql, "could not add fiscal year");
}

function update_fiscalyear($id, $closed)
{
    $sql = "UPDATE " . TB_PREF . "fiscal_year SET closed=" . db_escape($closed) . "
        WHERE id=" . db_escape($id);
    db_query($sql, "could not update fiscal year");
}

function get_all_fiscalyears()
{
    $sql = "SELECT * FROM " . TB_PREF . "fiscal_year ORDER BY begin";
    return db_query($sql, "could not get all fiscal years");
}

function get_all_new_fiscalyears()
{
    global $db_connections;
    $company = $_SESSION["wa_current_user"]->company;
    $connection = $db_connections[$company];
    $con = mysql_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"]);
    if (!$con) die('Could not connect: ' . mysql_error());
    mysql_select_db('wantedsettings', $con);
    $sql = "SELECT * FROM fiscal_years ORDER BY begin";
    $result = mysql_query($sql, $con);
    mysql_close($con);
    return $result;
}

function activated_fiscalyear($id)
{
    global $db_connections;
    $company = $_SESSION["wa_current_user"]->company;
    $connection = $db_connections[$company];
    $con = mysql_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"]);
    if (!$con) die('Could not connect: ' . mysql_error());
    mysql_select_db('wantedsettings', $con);
    $sql = "update fiscal_years set current = 0";
    mysql_query($sql, $con);
    $sql = "update fiscal_years set current = 1 where id = " . db_escape($id);
    mysql_query($sql, $con);
    mysql_close($con);
    meta_forward('../access/logout.php', '');
}

function add_new_fiscalyear()
{
    global $db_connections;
    $company = $_SESSION["wa_current_user"]->company;
    $connection = $db_connections[$company];
    $con = mysql_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"]);
    if (!$con) die('Could not connect: ' . mysql_error());
    mysql_select_db('wantedsettings', $con);
    $sql = "select max(dbname) as dbname from fiscal_years";
    $result = mysql_query($sql, $con);
    $myrow = mysql_fetch_array($result);
    $name_src = (int)$myrow['dbname'];
    $name_dst = $name_src + 1;
    $sql_update = "update fiscal_years set closed = 1 where dbname = '$name_src'";
    mysql_query($sql_update, $con);
    $sql = "INSERT INTO fiscal_years (dbname,begin,end) VALUES($name_dst,'$name_dst-01-01','$name_dst-12-31')";
    mysql_query($sql, $con);
    $id_dst = mysql_insert_id($con);
    mysql_close($con);
    /********************* START CONFIGURATION *********************/
    $DB_SRC_HOST = $connection["host"];
    $DB_SRC_USER = $connection["dbuser"];
    $DB_SRC_PASS = $connection["dbpassword"];
    $DB_SRC_NAME = $name_src;
    $DB_DST_HOST = $connection["host"];
    $DB_DST_USER = $connection["dbuser"];
    $DB_DST_PASS = $connection["dbpassword"];
    $DB_DST_NAME = $name_dst;
    $table_list = array();
    $trans_type = ST_JOURNAL;
    $trans_id = 1;
    $date_ = sql2date($name_dst . '-01-01');
    $from = sql2date($name_src . '-01-01');
    $to = sql2date($name_src . '-12-31');
    $from = add_days($from, -1);
    $to = add_days($to, 1);
    $ref = 'JUE0000001';
    set_global_connection();
    $laba_rugi = get_laba_rugi($from, $to);
    $laba_rugi_account = -(get_gl_balance_from_to($from, $to, '33000', 0, 0));
    $total_laba_rugi = $laba_rugi + $laba_rugi_account;
    /*********************** GRAB OLD SCHEMA ***********************/
    $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
    mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
    if (!mysql_query("update fiscal_year set closed = 1", $db1)) die(mysql_error());
    $result = mysql_query("SHOW TABLES;", $db1) or die(mysql_error());
    $buf = "set foreign_key_checks = 0;\n";
    $constraints = '';
    while ($row = mysql_fetch_array($result)) {
        array_push($table_list, $row[0]);
        $result2 = mysql_query("SHOW CREATE TABLE " . $row[0] . ";", $db1) or die(mysql_error());
        $res = mysql_fetch_array($result2);
        if (preg_match("/[ ]*CONSTRAINT[ ]+.*\n/", $res[1], $matches)) {
            $res[1] = preg_replace("/,\n[ ]*CONSTRAINT[ ]+.*\n/", "\n", $res[1]);
            $constraints .= "ALTER TABLE " . $row[0] . " ADD " . trim($matches[0]) . ";\n";
        }
        $buf .= $res[1] . ";\n";
    }
    $buf .= $constraints;
    $buf .= "set foreign_key_checks = 1";
    /**************** CREATE NEW DB WITH OLD SCHEMA ****************/
    $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
    $sql = 'CREATE DATABASE `' . $DB_DST_NAME . "`";
    if (!mysql_query($sql, $db2)) die(mysql_error());
    mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
    $queries = explode(';', $buf);
    foreach ($queries as $query) if (!mysql_query($query, $db2)) die(mysql_error());
    /********************* START CREATE FUNCTION *********************/
    $create_function = "CREATE FUNCTION `total_bayar`(`so_id` integer) RETURNS decimal(30,2)";
    $create_function .= "\nBEGIN";
    $create_function .= "\nDECLARE _hasil DECIMAL(30,2);";
    $create_function .= "\nSELECT IFNULL(SUM(c.amount),0) INTO _hasil FROM customers_trans c WHERE c.so_id = so_id AND c.void = 0;";
    $create_function .= "\nRETURN _hasil;";
    $create_function .= "\nEND";
    if (!mysql_query($create_function, $db2)) die(mysql_error());
    /********************* START COPY DATA *********************/
    $exclude_table = array(
        'audit_trail',
        'attachments',
        'bank_trans',
        'comments',
        'customers_trans',
        'debtor_trans',
        'debtor_trans_details',
        'gl_trans',
        'purch_orders',
        'purch_order_details',
        'refs',
        'sales_orders',
        'sales_order_details',
        'sales_order_item_details',
        'sql_trail',
        'stock_moves',
        'supp_invoice_items',
        'supp_trans',
        'supplier_trans',
        'voided',
        'budget_trans');
    foreach ($table_list as $m) {
        if (!in_array($m, $exclude_table)) {
            $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
            mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
            $query_data = _mysqldump_table_data_to_string($m, $db1);
            if ($query_data != '') {
                $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
                mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
                if (!mysql_query($query_data, $db2)) die(mysql_error());
            }
        }
    }
    $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
    mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
    if (!mysql_query("update fiscal_year set begin = '$name_dst-01-01', end = '$name_dst-12-31', closed = 0", $db2)
    ) die(mysql_error());
    //reset_sys_types($db2);
    $accounts = get_gl_accounts('11110', '32200');
    $currency = get_company_currency(); //'IDR'
    while ($account = db_fetch($accounts)) {
        $is_bank_to = is_bank_account($account['account_code']);
        $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
        mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
        $amount = get_gl_balance_from_to_custom($from, $to, $account['account_code'], $db1);
        $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
        mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
        add_gl_trans_custom($trans_type, $trans_id, $date_, $account['account_code'], 0, 0, $ref, $amount, $db2);
        if ($is_bank_to) {
            add_bank_trans_custom($trans_type, $trans_id, $is_bank_to, $ref, $date_, $amount, 0, "", $currency,
                "Cannot insert a destination bank transaction", $db2);
        }
    }
    $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
    mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
    add_gl_trans_custom($trans_type, $trans_id, $date_, '34000', 0, 0, $ref, $total_laba_rugi, $db2);
    $sql_ref = "REPLACE refs SET reference=" . db_escape($ref) . ", type=" . db_escape($trans_type) . ", id=" .
        db_escape($trans_id);
    $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
    mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
    mysql_query($sql_ref, $db2);
    $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
    mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
    $sql_belum_lunas = "SELECT order_no from sales_orders WHERE sales_orders.total != 0 and sales_orders.total > total_bayar(sales_orders.order_no)
    and sales_orders.void != 1";
    $result_belum_lunas = mysql_query($sql_belum_lunas, $db1);
    $order_id = array();
    while ($item = mysql_fetch_row($result_belum_lunas)) {
        $order_id[] = $item[0];
    }
    if (count($order_id) > 0) {
        /********************* SALES ORDERS *********************/
        $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
        mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
        $string_sales_order = _mysql_data_to_string('sales_orders', $db1,
            "where sales_orders.order_no in (" . implode(",", $order_id) . ")");
        $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
        mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
        mysql_query($string_sales_order, $db2);
        /********************* SALES ORDER DETAILS *********************/
        $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
        mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
        $string_sales_order_details = _mysql_data_to_string('sales_order_details', $db1,
            "WHERE sales_order_details.order_no in (" . implode(",", $order_id) . ")",
            array(0 => array('pos' => '0', 'value' => 'null')));
        $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
        mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
        mysql_query($string_sales_order_details, $db2);
        $sql_item_belum_lunas =
            "SELECT ref from sales_order_details WHERE sales_order_details.order_no in (" . implode(",", $order_id) .
                ")";
        $result_item_belum_lunas = mysql_query($sql_item_belum_lunas, $db1);
        /********************* SALES ORDER ITEM DETAILS *********************/
        $ref_item_ = array();
        while ($item = mysql_fetch_row($result_item_belum_lunas)) {
            $ref_item_[] = $item[0];
        }
        if (count($ref_item_) > 0) {
            $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
            mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
            $string_sales_order_item_details = _mysql_data_to_string('sales_order_item_details', $db1,
                "WHERE sales_order_item_details.id IN ('" . implode("','", $ref_item_) . "')");
            $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
            mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
            mysql_query($string_sales_order_item_details, $db2);
        }
        /********************* CUSTOMERS TRANS *********************/
        $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
        mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
        $customer_trans = _mysql_data_to_string('customers_trans', $db1,
            "WHERE customers_trans.so_id in (" . implode(",", $order_id) . ")",
            array(0 => array('pos' => '0', 'value' => 'null')));
        $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
        mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
        mysql_query($customer_trans, $db2);
    }
    $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
    mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
    $beli_belum_lunas = "SELECT order_no FROM purch_orders WHERE purch_orders.total > purch_orders.alloc";
    $result_beli_belum_lunas = mysql_query($beli_belum_lunas, $db1);
    $beli_order_no = array();
    while ($item = mysql_fetch_row($result_beli_belum_lunas)) {
        $beli_order_no[] = $item[0];
    }
    if (count($beli_order_no) > 0) {
        /********************* PURCH ORDERS *********************/
        $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
        mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
        $string_purch_order = _mysql_data_to_string('purch_orders', $db1,
            "where purch_orders.order_no in (" . implode(",", $beli_order_no) . ")");
        $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
        mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
        mysql_query($string_purch_order, $db2);
        /********************* PURCH ORDERS DETAILS *********************/
        $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
        mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
        $string_purch_order_details = _mysql_data_to_string('purch_order_details', $db1,
            "where purch_order_details.order_no in (" . implode(",", $beli_order_no) . ")",
            array(0 => array('pos' => '0', 'value' => 'null')));
        $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
        mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
        mysql_query($string_purch_order_details, $db2);
        /********************* SUPPLIER TRANS *********************/
        $db1 = mysql_connect($DB_SRC_HOST, $DB_SRC_USER, $DB_SRC_PASS) or die(mysql_error());
        mysql_select_db($DB_SRC_NAME, $db1) or die(mysql_error());
        $string_supplier_trans = _mysql_data_to_string('supplier_trans', $db1,
            "where supplier_trans.po_id in (" . implode(",", $beli_order_no) . ")",
            array(0 => array('pos' => '0', 'value' => 'null')));
        $db2 = mysql_connect($DB_DST_HOST, $DB_DST_USER, $DB_DST_PASS) or die(mysql_error());
        mysql_select_db($DB_DST_NAME, $db2) or die(mysql_error());
        mysql_query($string_supplier_trans, $db2);
    }
    activated_fiscalyear($id_dst);
}

function reset_sys_types($con)
{
    if (!mysql_query("update sys_types set next_reference = 'JUE0000002' where type_id = 0", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'KK00000001' where type_id = 1", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'KM00000001' where type_id = 2", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'TB00000001' where type_id = 4", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'SIN00000001' where type_id = 10", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'CKP0000001' where type_id = 11", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'PPL0000001' where type_id = 12", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'DNT00000001' where type_id = 13", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'TLK00000001' where type_id = 16", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'PST00000001' where type_id = 17", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'FPK00000001' where type_id = 18", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'FPT00000001' where type_id = 20", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'CKS00000001' where type_id = 21", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'PSU00000001' where type_id = 22", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'PT00000001' where type_id = 25", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'WO00000001' where type_id = 26", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'WOI00000001' where type_id = 28", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'PWO00000001' where type_id = 29", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'FPP00000001' where type_id = 30", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'PNW00000001' where type_id = 32", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'PBY0000001' where type_id = 35", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'DMS0000001' where type_id = 40", $con)) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'KB00000001' where type_id = 100", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'PKB00000001' where type_id = 101", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'KSK00000001' where type_id = 102", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'EST00000001' where type_id = 105", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'HPP00000001' where type_id = 112", $con)
    ) die(mysql_error());
    if (!mysql_query("update sys_types set next_reference = 'PPL0000001' where type_id = 115", $con)
    ) die(mysql_error());
}

function _mysqldump_table_data_to_string($table, $con)
{
    $query_builder = '';
    $sql = "select * from `$table`;";
    $result = mysql_query($sql, $con);
    if ($result) {
        $num_rows = mysql_num_rows($result);
        $num_fields = mysql_num_fields($result);
        if ($num_rows > 0) {
            //$query_builder .= "/* dumping data for table `$table` */\n";
            $field_type = array();
            $i = 0;
            while ($i < $num_fields) {
                $meta = mysql_fetch_field($result, $i);
                array_push($field_type, $meta->type);
                $i++;
            }
            //print_r( $field_type);
            $query_builder .= "insert into `$table` values";
            $index = 0;
            while ($row = mysql_fetch_row($result)) {
                $query_builder .= "(";
                for ($i = 0; $i < $num_fields; $i++) {
                    if (is_null($row[$i])) $query_builder .= "null"; else {
                        switch ($field_type[$i]) {
                            case 'int':
                                $query_builder .= $row[$i];
                                break;
                            case 'string':
                            case 'blob' :
                            default:
                                $query_builder .= "'" . mysql_real_escape_string($row[$i]) . "'";
                        }
                    }
                    if ($i < $num_fields - 1) $query_builder .= ",";
                }
                $query_builder .= ")";
                if ($index < $num_rows - 1) $query_builder .= ","; else
                    $query_builder .= ";";
                $query_builder .= "\n";
                $index++;
            }
        }
    }
    mysql_free_result($result);
    //$query_builder .= "\n";
    return $query_builder;
}

function _mysql_data_to_string($table, $con, $where = '', $replace = array())
{
    $query_builder = '';
    $sql = "select * from `$table` $where;";
    $result = mysql_query($sql, $con);
    if ($result) {
        $num_rows = mysql_num_rows($result);
        $num_fields = mysql_num_fields($result);
        if ($num_rows > 0) {
            //$query_builder .= "/* dumping data for table `$table` */\n";
            $field_type = array();
            $i = 0;
            while ($i < $num_fields) {
                $meta = mysql_fetch_field($result, $i);
                array_push($field_type, $meta->type);
                $i++;
            }
            //print_r( $field_type);
            $query_builder .= "insert into `$table` values";
            $index = 0;
            while ($row = mysql_fetch_row($result)) {
                $query_builder .= "(";
                for ($i = 0; $i < $num_fields; $i++) {
                    $is_replace = 0;
                    $count = count($replace);
                    for ($c = 0; $c < $count; $c++) {
                        $item_replace = $replace[$c];
                        if ($item_replace['pos'] == $i) {
                            $query_builder .= $item_replace['value'];
                            $is_replace = 1;
                            break;
                        }
                    }
                    if ($is_replace == 0) {
                        if (is_null($row[$i])) $query_builder .= "null"; else {
                            switch ($field_type[$i]) {
                                case 'int':
                                    $query_builder .= $row[$i];
                                    break;
                                case 'string':
                                case 'blob' :
                                default:
                                    $query_builder .= "'" . mysql_real_escape_string($row[$i]) . "'";
                            }
                        }
                    }
                    if ($i < $num_fields - 1) $query_builder .= ",";
                }
                $query_builder .= ")";
                if ($index < $num_rows - 1) $query_builder .= ","; else
                    $query_builder .= ";";
                $query_builder .= "\n";
                $index++;
            }
        }
    }
    mysql_free_result($result);
    //$query_builder .= "\n";
    return $query_builder;
}

function get_fiscalyear($id)
{
    $sql = "SELECT * FROM " . TB_PREF . "fiscal_year WHERE id=" . db_escape($id);
    $result = db_query($sql, "could not get fiscal year");
    return db_fetch($result);
}

function get_current_fiscalyear()
{
    global $path_to_root;
    include_once($path_to_root . "/admin/db/company_db.inc");
    $year = get_company_pref('f_year');
    $sql = "SELECT * FROM " . TB_PREF . "fiscal_year WHERE id=" . db_escape($year);
    $result = db_query($sql, "could not get current fiscal year");
    return db_fetch($result);
}

function delete_fiscalyear($id)
{
    begin_transaction();
    $sql = "DELETE FROM " . TB_PREF . "fiscal_year WHERE id=" . db_escape($id);
    db_query($sql, "could not delete fiscal year");
    commit_transaction();
}

function is_date_in_fiscalyears($date, $closed = true)
{
    $date = date2sql($date);
    $sql = "SELECT * FROM " . TB_PREF . "fiscal_year WHERE '$date' >= begin AND '$date' <= end";
    if (!$closed) $sql .= " AND closed=0";
    $result = db_query($sql, "could not get all fiscal years");
    return db_fetch($result) !== false;
}

function get_fiscalyear_begin_for_date($date)
{
    $date = date2sql($date);
    $sql = "SELECT begin FROM " . TB_PREF . "fiscal_year WHERE '$date' >= begin AND '$date' <= end";
    $result = db_query($sql, "could not get begin date of the fiscal year");
    $row = db_fetch_row($result);
    if ($row != false) return sql2date($row[0]); else
        return begin_fiscalyear();
}

function check_begin_end_date($date1, $date2)
{
    $sql = "SELECT MAX(end), MIN(begin) FROM " . TB_PREF . "fiscal_year";
    $result = db_query($sql, "could not retrieve last fiscal years");
    $row = db_fetch_row($result);
    if ($row[0] === null) return true;
    $max = add_days(sql2date($row[0]), 1);
    $min = add_days(sql2date($row[1]), -1);
    return ($max === $date1 || $min === $date2);
}

function next_begin_date()
{
    $sql = "SELECT MAX(end) FROM " . TB_PREF . "fiscal_year";
    $result = db_query($sql, "could not retrieve last fiscal years");
    $row = db_fetch_row($result);
    if ($row[0] === null) return false;
    return add_days(sql2date($row[0]), 1);
}

function check_years_before($date, $closed = false)
{
    $date = date2sql($date);
    $sql = "SELECT COUNT(*) FROM " . TB_PREF . "fiscal_year WHERE begin < '$date'";
    if (!$closed) $sql .= " AND closed=0";
    $result = db_query($sql, "could not check fiscal years before");
    $row = db_fetch_row($result);
    return ($row[0] > 0);
}

//---------------------------------------------------------------------------------------------
function close_year($year)
{
    $co = get_company_prefs();
    if (get_gl_account($co['retained_earnings_act']) == false || get_gl_account($co['profit_loss_year_act']) == false) {
        display_error(_("The Retained Earnings Account or the Profit and Loss Year Account has not been set in System and General GL Setup"));
        return false;
    }
    if (!is_account_balancesheet($co['retained_earnings_act']) || is_account_balancesheet($co['profit_loss_year_act'])
    ) {
        display_error(_("The Retained Earnings Account should be a Balance Account or the Profit and Loss Year Account should be an Expense Account (preferred the last one in the Expense Class)"));
        return false;
    }
    begin_transaction();
    $myrow = get_fiscalyear($year);
    $to = $myrow['end'];
    // retrieve total balances from balance sheet accounts
    $sql = "SELECT SUM(amount) FROM " . TB_PREF . "gl_trans INNER JOIN " . TB_PREF . "chart_master ON account=account_code
        INNER JOIN " . TB_PREF . "chart_types ON account_type=id INNER JOIN " . TB_PREF . "chart_class ON class_id=cid
        WHERE ctype>=" . CL_ASSETS . " AND ctype <=" . CL_EQUITY . " AND tran_date <= '$to'";
    $result = db_query($sql, "The total balance could not be calculated");
    $row = db_fetch_row($result);
    $balance = round2($row[0], user_price_dec());
    $to = sql2date($to);
    if ($balance != 0.0) {
        $trans_type = ST_JOURNAL;
        $trans_id = get_next_trans_no($trans_type);
        add_gl_trans($trans_type, $trans_id, $to, $co['retained_earnings_act'], 0, 0, _("Closing Year"), -$balance);
        add_gl_trans($trans_type, $trans_id, $to, $co['profit_loss_year_act'], 0, 0, _("Closing Year"), $balance);
    }
    close_transactions($to);
    commit_transaction();
    return true;
}

function open_year($year)
{
    $myrow = get_fiscalyear($year);
    $from = sql2date($myrow['begin']);
    begin_transaction();
    open_transactions($from);
    commit_transaction();
}

//---------------------------------------------------------------------------------------------
function delete_attachments_and_comments($type_no, $trans_no)
{
    $sql = "SELECT * FROM " . TB_PREF . "attachments WHERE type_no = $type_no AND trans_no = $trans_no";
    $result = db_query($sql, "Could not retrieve attachments");
    while ($row = db_fetch($result)) {
        $dir = company_path() . "/attachments";
        if (file_exists($dir . "/" . $row['unique_name'])) unlink($dir . "/" . $row['unique_name']);
        $sql = "DELETE FROM " . TB_PREF . "attachments WHERE  type_no = $type_no AND trans_no = $trans_no";
        db_query($sql, "Could not delete attachment");
    }
    $sql = "DELETE FROM " . TB_PREF . "comments WHERE  type = $type_no AND id = $trans_no";
    db_query($sql, "Could not delete comments");
    $sql = "DELETE FROM " . TB_PREF . "refs WHERE  type = $type_no AND id = $trans_no";
    db_query($sql, "Could not delete refs");
}

//---------------------------------------------------------------------------------------------
function delete_this_fiscalyear($selected_id)
{
    global $db_connections;
    db_backup($db_connections[$_SESSION["wa_current_user"]->company], 'Security backup before Fiscal Year Removal');
    begin_transaction();
    $ref = _("Open Balance");
    $myrow = get_fiscalyear($selected_id);
    $to = $myrow['end'];
    $sql = "SELECT order_no, trans_type FROM " . TB_PREF .
        "sales_orders WHERE ord_date <= '$to' AND type <> 1"; // don't take the templates
    $result = db_query($sql, "Could not retrieve sales orders");
    while ($row = db_fetch($result)) {
        $sql = "SELECT SUM(qty_sent), SUM(quantity) FROM " . TB_PREF .
            "sales_order_details WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}";
        $res = db_query($sql, "Could not retrieve sales order details");
        $row2 = db_fetch_row($res);
        if ($row2[0] == $row2[1]) {
            $sql = "DELETE FROM " . TB_PREF .
                "sales_order_details WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}";
            db_query($sql, "Could not delete sales order details");
            $sql = "DELETE FROM " . TB_PREF .
                "sales_orders WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}";
            db_query($sql, "Could not delete sales order");
            delete_attachments_and_comments($row['trans_type'], $row['order_no']);
        }
    }
    $sql = "SELECT order_no FROM " . TB_PREF . "purch_orders WHERE ord_date <= '$to'";
    $result = db_query($sql, "Could not retrieve purchase orders");
    while ($row = db_fetch($result)) {
        $sql = "SELECT SUM(quantity_ordered), SUM(quantity_received) FROM " . TB_PREF .
            "purch_order_details WHERE order_no = {$row['order_no']}";
        $res = db_query($sql, "Could not retrieve purchase order details");
        $row2 = db_fetch_row($res);
        if ($row2[0] == $row2[1]) {
            $sql = "DELETE FROM " . TB_PREF . "purch_order_details WHERE order_no = {$row['order_no']}";
            db_query($sql, "Could not delete purchase order details");
            $sql = "DELETE FROM " . TB_PREF . "purch_orders WHERE order_no = {$row['order_no']}";
            db_query($sql, "Could not delete purchase order");
            delete_attachments_and_comments(ST_PURCHORDER, $row['order_no']);
        }
    }
    $sql = "SELECT id FROM " . TB_PREF . "grn_batch WHERE delivery_date <= '$to'";
    $result = db_query($sql, "Could not retrieve grn batch");
    while ($row = db_fetch($result)) {
        $sql = "DELETE FROM " . TB_PREF . "grn_items WHERE grn_batch_id = {$row['id']}";
        db_query($sql, "Could not delete grn items");
        $sql = "DELETE FROM " . TB_PREF . "grn_batch WHERE id = {$row['id']}";
        db_query($sql, "Could not delete grn batch");
        delete_attachments_and_comments(25, $row['id']);
    }
    $sql = "SELECT trans_no, type FROM " . TB_PREF . "debtor_trans WHERE tran_date <= '$to' AND
        (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) = alloc";
    $result = db_query($sql, "Could not retrieve debtor trans");
    while ($row = db_fetch($result)) {
        if ($row['type'] == ST_SALESINVOICE) {
            $deliveries = get_sales_parent_numbers($row['type'], $row['trans_no']);
            foreach ($deliveries as $delivery) {
                $sql = "DELETE FROM " . TB_PREF .
                    "debtor_trans_details WHERE debtor_trans_no = $delivery AND debtor_trans_type = " . ST_CUSTDELIVERY;
                db_query($sql, "Could not delete debtor trans details");
                $sql =
                    "DELETE FROM " . TB_PREF . "debtor_trans WHERE trans_no = $delivery AND type = " . ST_CUSTDELIVERY;
                db_query($sql, "Could not delete debtor trans");
                delete_attachments_and_comments(ST_CUSTDELIVERY, $delivery);
            }
        }
        $sql = "DELETE FROM " . TB_PREF .
            "cust_allocations WHERE trans_no_from = {$row['trans_no']} AND trans_type_from = {$row['type']}";
        db_query($sql, "Could not delete cust allocations");
        $sql = "DELETE FROM " . TB_PREF .
            "debtor_trans_details WHERE debtor_trans_no = {$row['trans_no']} AND debtor_trans_type = {$row['type']}";
        db_query($sql, "Could not delete debtor trans details");
        $sql = "DELETE FROM " . TB_PREF . "debtor_trans WHERE trans_no = {$row['trans_no']} AND type = {$row['type']}";
        db_query($sql, "Could not delete debtor trans");
        delete_attachments_and_comments($row['type'], $row['trans_no']);
    }
    $sql = "SELECT trans_no, type FROM " . TB_PREF . "supp_trans WHERE tran_date <= '$to' AND
        ABS(ov_amount + ov_gst + ov_discount) = alloc";
    $result = db_query($sql, "Could not retrieve supp trans");
    while ($row = db_fetch($result)) {
        $sql = "DELETE FROM " . TB_PREF .
            "supp_allocations WHERE trans_no_from = {$row['trans_no']} AND trans_type_from = {$row['type']}";
        db_query($sql, "Could not delete supp allocations");
        $sql = "DELETE FROM " . TB_PREF .
            "supp_invoice_items WHERE supp_trans_no = {$row['trans_no']} AND supp_trans_type = {$row['type']}";
        db_query($sql, "Could not delete supp invoice items");
        $sql = "DELETE FROM " . TB_PREF . "supp_trans WHERE trans_no = {$row['trans_no']} AND type = {$row['type']}";
        db_query($sql, "Could not delete supp trans");
        delete_attachments_and_comments($row['type'], $row['trans_no']);
    }
    $sql = "SELECT id FROM " . TB_PREF . "workorders WHERE released_date <= '$to' AND closed=1";
    $result = db_query($sql, "Could not retrieve supp trans");
    while ($row = db_fetch($result)) {
        $sql = "SELECT issue_no FROM " . TB_PREF . "wo_issues WHERE workorder_id = {$row['id']}";
        $res = db_query($sql, "Could not retrieve wo issues");
        while ($row2 = db_fetch_row($res)) {
            $sql = "DELETE FROM " . TB_PREF . "wo_issue_items WHERE issue_id = {$row2[0]}";
            db_query($sql, "Could not delete wo issue items");
        }
        delete_attachments_and_comments(ST_MANUISSUE, $row['id']);
        $sql = "DELETE FROM " . TB_PREF . "wo_issues WHERE workorder_id = {$row['id']}";
        db_query($sql, "Could not delete wo issues");
        $sql = "DELETE FROM " . TB_PREF . "wo_manufacture WHERE workorder_id = {$row['id']}";
        db_query($sql, "Could not delete wo manufacture");
        $sql = "DELETE FROM " . TB_PREF . "wo_requirements WHERE workorder_id = {$row['id']}";
        db_query($sql, "Could not delete wo requirements");
        $sql = "DELETE FROM " . TB_PREF . "workorders WHERE id = {$row['id']}";
        db_query($sql, "Could not delete workorders");
        delete_attachments_and_comments(ST_WORKORDER, $row['id']);
    }
    $sql = "SELECT loc_code, stock_id, SUM(qty) AS qty, SUM(qty*standard_cost) AS std_cost FROM " . TB_PREF . "stock_moves WHERE tran_date <= '$to' GROUP by
        loc_code, stock_id";
    $result = db_query($sql, "Could not retrieve supp trans");
    while ($row = db_fetch($result)) {
        $sql = "DELETE FROM " . TB_PREF .
            "stock_moves WHERE tran_date <= '$to' AND loc_code = '{$row['loc_code']}' AND stock_id = '{$row['stock_id']}'";
        db_query($sql, "Could not delete stock moves");
        $qty = $row['qty'];
        $std_cost = ($qty == 0 ? 0 : round2($row['std_cost'] / $qty, user_price_dec()));
        $sql = "INSERT INTO " . TB_PREF . "stock_moves (stock_id, loc_code, tran_date, reference, qty, standard_cost) VALUES
            ('{$row['stock_id']}', '{$row['loc_code']}', '$to', '$ref', $qty, $std_cost)";
        db_query($sql, "Could not insert stock move");
    }
    $sql = "DELETE FROM " . TB_PREF . "voided WHERE date_ <= '$to'";
    db_query($sql, "Could not delete voided items");
    $sql = "DELETE FROM " . TB_PREF . "trans_tax_details WHERE tran_date <= '$to'";
    db_query($sql, "Could not delete trans tax details");
    $sql = "DELETE FROM " . TB_PREF . "exchange_rates WHERE date_ <= '$to'";
    db_query($sql, "Could not delete exchange rates");
    $sql = "DELETE FROM " . TB_PREF . "budget_trans WHERE tran_date <= '$to'";
    db_query($sql, "Could not delete exchange rates");
    $sql =
        "SELECT account, SUM(amount) AS amount FROM " . TB_PREF . "gl_trans WHERE tran_date <= '$to' GROUP by account";
    $result = db_query($sql, "Could not retrieve gl trans");
    $trans_no = get_next_trans_no(ST_JOURNAL);
    $new = false;
    while ($row = db_fetch($result)) {
        $sql = "DELETE FROM " . TB_PREF . "gl_trans WHERE tran_date <= '$to' AND account = '{$row['account']}'";
        db_query($sql, "Could not delete gl trans");
        if (is_account_balancesheet($row['account'])) {
            $sql = "INSERT INTO " . TB_PREF . "gl_trans (type, type_no, tran_date, account, memo_, amount) VALUES
                (" . ST_JOURNAL . ", $trans_no, '$to', '{$row['account']}', '$ref', {$row['amount']})";
            db_query($sql, "Could not insert gl trans");
            $new = true;
        }
    }
    if ($new) {
        global $Refs;
        $trans_type = ST_JOURNAL;
        $reference = $Refs->get_next($trans_type);
        $Refs->save($trans_type, $trans_no, $reference);
        $date_ = sql2date($to);
        add_audit_trail($trans_type, $trans_no, $date_);
    }
    $sql = "SELECT bank_act, SUM(amount) AS amount FROM " . TB_PREF .
        "bank_trans WHERE trans_date <= '$to' GROUP BY bank_act";
    $result = db_query($sql, "Could not retrieve bank trans");
    while ($row = db_fetch($result)) {
        $sql = "DELETE FROM " . TB_PREF . "bank_trans WHERE trans_date <= '$to' AND bank_act = '{$row['bank_act']}'";
        db_query($sql, "Could not delete bank trans");
        $sql = "INSERT INTO " . TB_PREF . "bank_trans (type, trans_no, trans_date, bank_act, ref, amount) VALUES
            (0, 0, '$to', '{$row['bank_act']}', '$ref', {$row['amount']})";
        db_query($sql, "Could not insert bank trans");
    }
    $sql = "DELETE FROM " . TB_PREF . "audit_trail WHERE gl_date <= '$to'";
    db_query($sql, "Could not delete audit trail");
    $sql = "SELECT type, id FROM " . TB_PREF . "comments WHERE type != " . ST_SALESQUOTE . " AND type != " .
        ST_SALESORDER . " AND type != " . ST_PURCHORDER;
    $result = db_query($sql, "Could not retrieve comments");
    while ($row = db_fetch($result)) {
        $sql = "SELECT count(*) FROM " . TB_PREF . "gl_trans WHERE type = {$row['type']} AND type_no = {$row['id']}";
        $res = db_query($sql, "Could not retrieve gl_trans");
        $row2 = db_fetch_row($res);
        if ($row2[0] == 0) // if no link, then delete comments
        {
            $sql = "DELETE FROM " . TB_PREF . "comments WHERE type = {$row['type']} AND id = {$row['id']}";
            db_query($sql, "Could not delete comments");
        }
    }
    $sql = "SELECT type, id FROM " . TB_PREF . "refs WHERE type != " . ST_SALESQUOTE . " AND type != " . ST_SALESORDER .
        " AND type != " . ST_PURCHORDER;
    $result = db_query($sql, "Could not retrieve refs");
    while ($row = db_fetch($result)) {
        $sql = "SELECT count(*) FROM " . TB_PREF . "gl_trans WHERE type = {$row['type']} AND type_no = {$row['id']}";
        $res = db_query($sql, "Could not retrieve gl_trans");
        $row2 = db_fetch_row($res);
        if ($row2[0] == 0) // if no link, then delete refs
        {
            $sql = "DELETE FROM " . TB_PREF . "refs WHERE type = {$row['type']} AND id = {$row['id']}";
            db_query($sql, "Could not delete refs");
        }
    }
    delete_fiscalyear($selected_id);
    commit_transaction();
}

?>

